﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;

/// <summary>
/// ChannelDao 的摘要说明
/// </summary>
public class ChannelDao
{
	public ChannelDao()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}
    //添加
    public static void add(string name, string father_id, string show_order, string url,string is_show,string img,string pageLocation)
    {
        OleDbConnection conn = DB.conn();
        if (conn != null )
        {
            if (father_id.Trim().Length == 0) father_id = "1";
            if (show_order.Trim().Length == 0) show_order = "0";
            conn.Open();
            OleDbCommand comm = new OleDbCommand();
            comm.Connection = conn;
            comm.CommandText = "insert into channel (name, father_id, show_order, url,is_show,img,pageLocation) values('" + name + "',"
                + father_id + "," + show_order + ",'" + url + "','" + is_show + "','" + img + "','" + pageLocation + "')";
            try { comm.ExecuteNonQuery(); }
            catch (Exception ex) { throw ex; }
            finally { comm.Dispose(); conn.Close(); }
        }
    }
    //更新
    public static void update(string id, string name,  string show_order, string url, string is_show, string img, string pageLocation)
    {
        OleDbConnection conn = DB.conn();
        if (conn != null )
        {
            if (show_order.Trim().Length == 0) show_order = "0";
            conn.Open();
            OleDbCommand comm = new OleDbCommand();
            comm.Connection = conn;
            comm.CommandText = "update channel set name='" + name + "', show_order=" + show_order
                + ",url='" + url + "',is_show='" + is_show + "',img='" + img + "',pageLocation='" + pageLocation + "' where channel_id=" + id;
            try { comm.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { comm.Dispose(); conn.Close(); }
        }
    }

    //删除
    public static void del(string id)
    {
        OleDbConnection conn = DB.conn();
        if (conn != null)
        {
            conn.Open();
            OleDbCommand comm = new OleDbCommand();
            OleDbCommand delComm = new OleDbCommand();
            try
            {
                comm.Connection = conn;
                //先删除子栏目
                comm.CommandText = "select * from channel where father_id="+id;
                DataTable dt = new DataTable();
                OleDbDataAdapter da = new OleDbDataAdapter();
                da.SelectCommand = comm;
                da.Fill(dt);
                if (dt != null && dt.Rows.Count > 0) {
                    for (int i = 0; i < dt.Rows.Count; i++) {
                        if (dt.Rows[i]["channel_id"] != null && dt.Rows[i]["channel_id"].ToString().Trim().Length>0)
                            del(dt.Rows[i]["channel_id"].ToString().Trim());
                    }
                }
               
                //删除
                delComm.Connection = conn;
                delComm.CommandText = "delete from channel where channel_id=" + id;
                delComm.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { comm.Dispose(); delComm.Dispose(); conn.Close(); }
        }
    }
    /// <summary>
    /// 根据父栏目页面位置获取子栏目
    /// </summary>
    /// <param name="parPl"></param>
    public static DataTable findSonByParPL(String parPl, String isShow)
    {
        OleDbConnection conn = DB.conn();
        if (conn != null)
        {
            conn.Open();
            OleDbCommand comm = new OleDbCommand();
            comm.Connection = conn;
            comm.CommandText = "select * from channel where father_id in (select channel_id from channel where pageLocation='" + parPl + "') " + (isShow != null && isShow.Trim().Length > 0 ? "and is_show='" + isShow + "'" : "") + " order by show_order";
            try
            {
                OleDbDataAdapter da = new OleDbDataAdapter();
                da.SelectCommand = comm;
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex) { throw ex; }
            finally { comm.Dispose(); conn.Close(); }
        }
        return null;
    }
    /// <summary>
    /// 根据父栏目id获取子栏目
    /// </summary>
    /// <param name="parPl"></param>
    public static DataTable findSonByParId(String parId, String isShow)
    {
        OleDbConnection conn = DB.conn();
        if (conn != null)
        {
            conn.Open();
            OleDbCommand comm = new OleDbCommand();
            comm.Connection = conn;
            comm.CommandText = "select * from channel where father_id =" + parId + " order by show_order";
            try
            {
                OleDbDataAdapter da = new OleDbDataAdapter();
                da.SelectCommand = comm;
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex) { throw ex; }
            finally { comm.Dispose(); conn.Close(); }
        }
        return null;
    }
    /// <summary>
    /// 根据页面位置查询栏目 
    /// </summary>
    /// <param name="pl"></param>
    /// <returns></returns>
    public static DataTable findByPL(String pl)
    {
        OleDbConnection conn = DB.conn();
        if (conn != null)
        {
            conn.Open();
            OleDbCommand comm = new OleDbCommand();
            comm.Connection = conn;
            comm.CommandText = "select * from channel where pageLocation='" + pl + "'";
            try
            {
                OleDbDataAdapter da = new OleDbDataAdapter();
                da.SelectCommand = comm;
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex) { throw ex; }
            finally { comm.Dispose(); conn.Close(); }
        }
        return null;
    }
    /// <summary>
    /// 根据id查询栏目 
    /// </summary>
    /// <param name="pl"></param>
    /// <returns></returns>
    public static DataTable findById(String id)
    {
        OleDbConnection conn = DB.conn();
        if (conn != null)
        {
            conn.Open();
            OleDbCommand comm = new OleDbCommand();
            comm.Connection = conn;
            comm.CommandText = "select * from channel where channel_id=" + id;
            try
            {
                OleDbDataAdapter da = new OleDbDataAdapter();
                da.SelectCommand = comm;
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex) { throw ex; }
            finally { comm.Dispose(); conn.Close(); }
        }
        return null;
    }
}
